<?php
	// Change settings to display errors
    ini_set("display_errors", true);
    ini_set("session.use_only_cookies", "on");
    error_reporting(E_ALL ^ E_NOTICE ^ E_WARNING);
	
	// Connect to the server and database
    define("DB", "pickupmap");
	define("DBUSER", "pick");
	define("DBPASS", "meup");
	define("DBSERVER", "localhost");

	$error = "";
	$output = "";	
	
	if (($connection = mysql_connect(DBSERVER, DBUSER, DBPASS)) == FALSE) 
		$error = "Database connection error: " . mysql_error();

	if (mysql_select_db(DB, $connection) == FALSE) 
		$error = "Database selection error: " . mysql_error();
		
    // Parse post inputs
	foreach ($_POST as $key => $value)
		$$key = mysql_real_escape_string($value);

	// TEST CODE
//	echo ("<html><head></head><body>");
		
// 	* event_create *	
//	$post_action = "event_create";
//	$f_id = 6;
//	$e_sport = "soccer";
//	$e_timestart = "2011-02-27 01:35:00";
//	$e_duration = 180;
//	$e_numppl = 5;
//	$e_equipment = 1;
//	$e_skill = "casual";

// 	* field_find *	
//	$post_action = "event_checkin";
//	$e_id = 1;
//	$e_numppl = 2;
//	$e_equipment = 1;

// 	* field_find *	
//	$post_action = "field_find";
//	$f_id = "*";
//	$f_name = "*";
//	$f_long = "*";
//	$f_lat = "*";
//	$f_hasopenfield = "*";
//	$f_hasdiamond = "1";
//	$f_hasbball = "*";

// 	* event_find *	
//	$post_action = "event_find";
//	$e_id = "*";
//	$f_id = "*";
//	$e_sport = "*";
//	$e_timestart = "*";
//	$e_timestop = "*";
//	$e_numppl = "*";
//	$e_equipment = "*";
//	$e_skill= "*";
		
	// Create an event
	//	Inputs: 
	//		f_id (integer): primary key 
	//		e_sport (string): "basketball", "soccer", "baseball", etc.
	//		e_timestart (datetime): YYYY-MM-DD HH:MM:SS
	//		e_timestop (datetime): YYYY-MM-DD HH:MM:SS
	//			OPTIONAL: e_duration (integer) instead
	//		e_numppl (integer)
	//		e_equipment (integer): 1 = yes, 0 = no
	//		e_skill (string): "competitive", "anything goes"		
	if ($post_action == "event_create"){
		if (!isset($e_timestop)) $e_timestop = date("o-m-d H:i:s",strtotime($e_timestart) + ($e_duration * 60));
		
	    $query = sprintf("INSERT INTO events (f_id, e_sport, e_timestart, e_timestop, e_numppl, e_equipment, e_skill)
			VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s')",
			$f_id, $e_sport, $e_timestart, $e_timestop, $e_numppl, $e_equipment, $e_skill);
		mysql_query($query);
		
		if (mysql_affected_rows() == 1) $output = "SUCCESS: ".$query;
		else $error = "ERROR: ".$query;
	}
	// Checkin
	//	Inputs: 
	//		e_id (integer): primary key
	//		e_numppl (integer)
	//		e_equipment (integer): 1 = yes, 0 = no
	elseif ($post_action == "event_checkin"){
		$query = "SELECT * FROM events WHERE e_id = ".$e_id;
		$result = mysql_query($query);
		$row = mysql_fetch_array($result);
		
		$e_numppl = $e_numppl + $row["e_numppl"];
		
		if ($e_equipment == 1) $query = "UPDATE events SET e_numppl = ".$e_numppl.", e_equipment = 1 WHERE e_id = ".$e_id;
		else $query = "UPDATE events SET e_numppl = ".$e_numppl." WHERE e_id = ".$e_id;
		
		mysql_query($query);
		
		if (mysql_affected_rows() == 1) $output = "SUCCESS: ".$query;
		else $error = "ERROR: ".$query;
	} 
	// Find an event
	//	Inputs: 
	//		e_id (integer): primary key 
	//		e_sport (string): "basketball", "soccer", "baseball", etc.
	//		e_timestart (datetime): YYYY-MM-DD HH:MM:SS
	//		e_timestop (datetime): YYYY-MM-DD HH:MM:SS
	//			NOTE: returns events with a START time between the input e_timestart and e_timestop
	//		e_numppl (integer)
	//		e_equipment (integer): 1 = yes, 0 = no
	//		e_skill (string): "competitive", "anything goes"
	//	Output:
	//		JSON array output with all parameters
	//		[{"e_id":"1","e_sport":"basketbal"...},...]
	//		Variables: e_id, f_id, e_sport, e_timestart, e_timestop, e_numppl, e_equipment, e_skill, f_name
	elseif ($post_action == "event_find"){
		$parameters = "";
		if ($e_id != "*") $parameters = $parameters."e_id = ".$e_id." AND ";
		if ($f_id != "*") $parameters = $parameters."f_id = ".$f_id." AND ";
		if ($e_sport != "*") $parameters = $parameters."e_sport = ".$e_sport." AND ";
		if ($e_timestart != "*") $parameters = $parameters."(e_timestart BETWEEN ".$e_timestart." AND ".$e_timestop.") AND ";
		if ($e_numppl != "*") $parameters = $parameters."e_numppl = ".$e_numppl." AND ";
		if ($e_equipment != "*") $parameters = $parameters."e_equipment = ".$e_equipment." AND ";
		if ($e_skill != "*") $parameters = $parameters."e_skill = ".$e_skill." AND ";
		$parameters = $parameters."e_id";
		 
		$query = "SELECT * FROM events WHERE ".$parameters;
		$result = mysql_query($query);
		
		if (mysql_num_rows($result) != 0) {
			$output = "[";
			
			while ($row = mysql_fetch_array($result)){
				$output = $output.'{"e_id":"'.$row["e_id"].'",';
				$output = $output.'"f_id":"'.$row["f_id"].'",';
				$output = $output.'"e_sport":"'.$row["e_sport"].'",';
				$output = $output.'"e_timestart":"'.$row["e_timestart"].'",';
				$output = $output.'"e_timestop":"'.$row["e_timestop"].'",';
				$output = $output.'"e_numppl":"'.$row["e_numppl"].'",';
				$output = $output.'"e_equipment":"'.$row["e_equipment"].'",';
				$output = $output.'"e_skill":"'.$row["e_skill"].'",';
				
				$query = "SELECT * FROM fields WHERE f_id = ".$row["f_id"];
				$result_field = mysql_query($query);
				$row_field = mysql_fetch_array($result_field);
				
				$output = $output.'"f_name":"'.$row_field["f_name"].'"},';
			}
			
			$output = substr($output,0,strlen($output)-1);
			$output = $output."]";
		}
//		echo $output;
	}
	// Create an event
	//	Inputs: 
	//		f_id (integer): primary key 
	//		f_name (string): "esplanade", etc.
	//		f_long (float): 12.3456
	//		f_lat (float): 12.3456
	//		f_hasopenfield (integer): 1 = yes, 0 = no
	//		f_hasdiamond (integer): 1 = yes, 0 = no
	//		f_hasbball (integer): 1 = yes, 0 = no
	//	Output:
	//		JSON array output with all parameters
	//		[{"f_id":"1","f_name":"esplanade"...},...]
	//		Variables: f_id, f_name, f_long, f_lat, f_hasopenfield, f_hasdiamond, f_hasbball, f_game, events
	elseif ($post_action == "field_find"){
		$parameters = "";
		if ($f_id != "*") $parameters = $parameters."f_id = ".$f_id." AND ";
		if ($f_name != "*") $parameters = $parameters."f_name = ".$f_name." AND ";
		if ($f_long != "*") $parameters = $parameters."f_long = ".$f_long." AND ";
		if ($f_lat != "*") $parameters = $parameters."f_lat = ".$f_lat." AND ";
		if ($f_hasopenfield != "*" || $f_hasdiamond != "*" || $f_hasbball != "*"){
			$parameters = $parameters."(";
			  
			if ($f_hasopenfield != "*") {
				$parameters = $parameters."f_hasopenfield = ".$f_hasopenfield;
				if ($f_hasdiamond != "*") $parameters = $parameters." OR f_hasdiamond = ".$f_hasdiamond;
				if ($f_hasbball != "*") $parameters = $parameters." OR f_hasbball = ".$f_hasbball;
			}
			elseif ($f_hasdiamond != "*") {
				$parameters = $parameters." f_hasdiamond = ".$f_hasdiamond;
				if ($f_hasbball != "*") $parameters = $parameters." OR f_hasbball = ".$f_hasbball;
			}
			elseif ($f_hasbball != "*") $parameters = $parameters." f_hasbball = ".$f_hasbball;
			
			$parameters = $parameters.") AND ";
		}
		$parameters = $parameters."f_id";
		
	    $query = "SELECT * FROM fields WHERE ".$parameters;
		$result = mysql_query($query);
		
//		echo $query;

		if (mysql_num_rows($result) == 0) $output = null;
		else {
			$output = "[";
			
			while ($row = mysql_fetch_array($result)){
				$output = $output.'{"f_id":"'.$row["f_id"].'",';
				$output = $output.'"f_name":"'.$row["f_name"].'",';
				$output = $output.'"f_long":"'.$row["f_long"].'",';
				$output = $output.'"f_lat":"'.$row["f_lat"].'",';
				$output = $output.'"f_hasopenfield":"'.$row["f_hasopenfield"].'",';
				$output = $output.'"f_hasdiamond":"'.$row["f_hasdiamond"].'",';
				$output = $output.'"f_hasbball":"'.$row["f_hasbball"].'",';
				
				$endofday = '"'.date("o-m-d").' 23:59:00"';
				
				$query = "SELECT * FROM events WHERE f_id = ".$row["f_id"]." AND e_timestart BETWEEN NOW() AND ".$endofday;
				$result_event = mysql_query($query);
				
				if (mysql_num_rows($result_event) == 0) $output = $output.'"events":""'; 
				else {
					$output = $output.'"events":[';

					while ($row_event = mysql_fetch_array($result_event)){
						$output = $output.'{"e_id":"'.$row_event["e_id"].'",';
						$output = $output.'"e_sport":"'.$row_event["e_sport"].'"},';
					}
					
					$output = substr($output,0,strlen($output)-1);
					$output = $output."]";
				}
				
				$output = $output."},";
			}
			
			$output = substr($output,0,strlen($output)-1);
			$output = $output."]";
			
//			echo $output;
		}
	}
	else {
		$error = "No recognized post_action";
	}
	
//	echo ("</body></html>");
	
	if ($output == "") $output = '""';
	echo ('{ "output":'.$output.',"error":'.json_encode($error).' }');
?>